//    Copyright (C) 2013  <lugocreation@gmail.com>
//
//    This program is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with this program.  If not, see <http://www.gnu.org/licenses/>
package org.jpgolay.wi.persistence;

import java.sql.*;
import java.util.*;
import org.jpgolay.wi.agency.Agency;
import org.jpgolay.wi.availability.Availability;
import org.jpgolay.wi.customer.Customer;
import org.jpgolay.wi.group.Group;
import org.jpgolay.wi.realestate.RealEstate;
import org.jpgolay.wi.salesperson.SalesPerson;
import org.jpgolay.wi.user.User;
import org.jpgolay.wi.util.WIDate;

// Referenced classes of package org.jpgolay.wi.persistence:
//            ConnectDB, SQLList
public class ObjectCRUD {

    public ObjectCRUD() {
        init();
    }

    public Integer create(int type) {
        return createSQL(type);
    }

    public Object read(Integer pk, int type) {
        return readSQL(pk, type);
    }

    public Collection readAll(Integer pk, int type) {
        return readSQLAll(pk, type);
    }

    public Object readBy(String arg0, String arg1, int type) {
        return readSQLBy(arg0, arg1, type);
    }

    public boolean update(Object obj, int type) {
        return updateSQL(obj, type);
    }

    public boolean delete(Integer pk, String lastUpdate, int type) {
        return deleteSQL(pk, lastUpdate, type);
    }

    private void init() {
        con = ConnectDB.getConnection();
        date = new WIDate();
    }

    private Integer createSQL(int type) {
        try {
            switch (type) {
                case 1: // '\001'
                {
                    pst = con.prepareStatement("INSERT INTO USER (ID,LAST_UPDATE) VALUES (?,?)");
                    Integer id = getNewId("SELECT MAX(ID)+1 FROM USER");
                    pst.setInt(1, id.intValue());
                    pst.setString(2, (new WIDate()).getDateTimeDB());
                    pst.execute();
                    return id;
                }

                case 4: // '\004'
                {
                    pst = con.prepareStatement("INSERT INTO REALESTATE (ID,LAST_UPDATE) VALUES (?,?)");
                    Integer id = getNewId("SELECT MAX(ID)+1 FROM REALESTATE");
                    pst.setInt(1, id.intValue());
                    pst.setString(2, (new WIDate()).getDateTimeDB());
                    pst.execute();
                    return id;
                }

                case 5: // '\005'
                {
                    pst = con.prepareStatement("INSERT INTO AGENCY (ID,LAST_UPDATE) VALUES (?,?)");
                    Integer id = getNewId("SELECT MAX(ID)+1 FROM AGENCY");
                    pst.setInt(1, id.intValue());
                    pst.setString(2, (new WIDate()).getDateTimeDB());
                    pst.execute();
                    return id;
                }

                case 6: // '\006'
                {
                    pst = con.prepareStatement("INSERT INTO GROUPS (ID,LAST_UPDATE) VALUES (?,?)");
                    Integer id = getNewId("SELECT MAX(ID)+1 FROM GROUPS");
                    pst.setInt(1, id.intValue());
                    pst.setString(2, (new WIDate()).getDateTimeDB());
                    pst.execute();
                    return id;
                }

                case 7: // '\007'
                {
                    pst = con.prepareStatement("INSERT INTO AVAILABILITY (ID,LAST_UPDATE) VALUES (?,?)");
                    Integer id = getNewId("SELECT MAX(ID)+1 FROM AVAILABILITY");
                    pst.setInt(1, id.intValue());
                    pst.setString(2, (new WIDate()).getDateTimeDB());
                    pst.execute();
                    return id;
                }

                case 8: // '\b'
                {
                    pst = con.prepareStatement("INSERT INTO CUSTOMER (ID,LASTTIME) VALUES (?,?)");
                    Integer id = getNewId("SELECT MAX(ID)+1 FROM CUSTOMER");
                    pst.setInt(1, id.intValue());
                    pst.setString(2, (new WIDate()).getDateTimeDB());
                    pst.execute();
                    return id;
                }

                case 9: // '\t'
                {
                    pst = con.prepareStatement("INSERT INTO SALESPERSON (ID,LASTTIME) VALUES (?,?)");
                    Integer id = getNewId("SELECT MAX(ID)+1 FROM SALESPERSON");
                    pst.setInt(1, id.intValue());
                    pst.setString(2, (new WIDate()).getDateTimeDB());
                    pst.execute();
                    return id;
                }

                case 2: // '\002'
                case 3: // '\003'
                default: {
                    return null;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private Object readSQL(Integer pk, int type) {
        try {
            switch (type) {
                case 1: // '\001'
                    pst = con.prepareStatement("SELECT ID,GROUP_ID,USERNAME,PASS,LASTNAME,FIRSTNAME,ADDR_LN1,ADDR_LN2,CITY,COUNTY,ZIP,TEL1,TEL2,FAX,EMAIL,WEB,AGENCY_ID,ACCESS,EXPIRY_DATE,PAYMENT_DUE,COMMENT,LOGO,LAST_UPDATE,LAST_USER FROM USER WHERE ID=?");
                    pst.setInt(1, pk.intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        user = new User();
                        user.setId(new Integer(rs.getInt(1)));
                        user.setGroupId(new Integer(rs.getInt(2)));
                        user.setUsername(rs.getString(3));
                        user.setPass(rs.getString(4));
                        user.setLastname(rs.getString(5));
                        user.setFirstname(rs.getString(6));
                        user.setAddrLn1(rs.getString(7));
                        user.setAddrLn2(rs.getString(8));
                        user.setCity(rs.getString(9));
                        user.setCounty(rs.getString(10));
                        user.setZip(rs.getString(11));
                        user.setTel1(rs.getString(12));
                        user.setTel2(rs.getString(13));
                        user.setFax(rs.getString(14));
                        user.setEmail(rs.getString(15));
                        user.setWeb(rs.getString(16));
                        user.setAgencyId(new Integer(rs.getInt(17)));
                        user.setAccess(new Integer(rs.getInt(18)));
                        user.setExpiryDate(rs.getString(19));
                        user.setPaymentDue(rs.getBigDecimal(20));
                        user.setComment(rs.getString(21));
                        user.setLogo(rs.getBytes(22));
                        user.setLastUpdate(rs.getString(23));
                        user.setLastUser(rs.getString(24));
                        return user;
                    } else {
                        return null;
                    }

                case 4: // '\004'
                    pst = con.prepareStatement("SELECT ADDR_LN1,ADDR_LN2,AGENCY_FEES,ARCHIVE,AREA,ASPECT,AVAILABILITY,BALCONY,BATCH,BATHROOM,BED,BEDROOM,CATEGORY,CELLAR,CHARGE,CHARGE_DEPOSIT,CONDITIONS,COUNTY,COUNTRY,CREATEDATE,CUPBOARD,DEPARTURE,DEPARTURE_CUSTOMER,DEPOSIT,DESCRIPTION,ELEVATOR,FIREPLACE,FLOOR,FURNISHED,GARAGE,GARDEN,HALL,HEATING,ID,ID_CUSTOMER,BUNCHKEY,KITCHEN,LAST_UPDATE,LAST_USER,LIFEANNUITY,LIVINGROOM,LOGGIA,MEZZANINE,NUM_CONSULT_CC,NUM_CONSULT_WEB,OBSERVATION,ONLYOPTION,OPTION_FISC,PANEL,PARKING,PHOTO1,PHOTO2,PHOTO3,PHOTO4,PHOTO5,PHOTO6,PHOTO7,PHOTO8,PHOTO9,PHOTO10,PHOTOS_VALID,POOL,PLOT,PRINT_OBSERVATION,REF,REF_INTERNAL,RENT,RESERVE,RESTROOM,ROOFGARDEN,SALE,SECTOR,SHOWER,SORT,STANDING,STATE,STYLEFLOOR,SURFACE_FLOOR,SURFACE_GROUND,TV,TYPE,VACATION,WEB,ZIP,AGENCY_ID,EXIT_CUSTOMER,S_GARAGE,S_LIVINGROOM,S_HALL,S_ROOFGARDEN,S_MEZZANINE,S_LOGGIA,S_ROOM,S_CELLAR,CUSTOMER,RESIDANCE,S_KITCHEN,S_BALCONY,CELLAR_WINE,WALLS,DUPLEX,S_CELLAR_WINE FROM REALESTATE WHERE ID=?");
                    pst.setInt(1, pk.intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        realEstate = new RealEstate();
                        realEstate.setAddrLn1(rs.getString(1));
                        realEstate.setAddrLn2(rs.getString(2));
                        realEstate.setAgencyFees(rs.getBigDecimal(3));
                        realEstate.setArchive(rs.getString(4));
                        realEstate.setArea(rs.getString(5));
                        realEstate.setAspect(rs.getString(6));
                        realEstate.setAvailability(rs.getString(7));
                        realEstate.setBalcony(rs.getString(8));
                        realEstate.setBatch(rs.getString(9));
                        realEstate.setBathroom(rs.getString(10));
                        realEstate.setBed(rs.getString(11));
                        realEstate.setBedroom(rs.getString(12));
                        realEstate.setCategory(rs.getString(13));
                        realEstate.setCellar(rs.getString(14));
                        realEstate.setCharge(rs.getBigDecimal(15));
                        realEstate.setChargeDeposit(rs.getBigDecimal(16));
                        realEstate.setCondition(rs.getString(17));
                        realEstate.setCounty(rs.getString(18));
                        realEstate.setCountry(rs.getString(19));
                        realEstate.setCreate(rs.getString(20));
                        realEstate.setCupboard(rs.getString(21));
                        realEstate.setDeparture(rs.getString(22));
                        realEstate.setDepartureCustomer(rs.getString(23));
                        realEstate.setDeposit(rs.getBigDecimal(24));
                        realEstate.setDescription(rs.getString(25));
                        realEstate.setElevator(rs.getString(26));
                        realEstate.setFireplace(rs.getString(27));
                        realEstate.setFloor(rs.getString(28));
                        realEstate.setFurnished(rs.getString(29));
                        realEstate.setGarage(rs.getString(30));
                        realEstate.setGarden(rs.getString(31));
                        realEstate.setHall(rs.getString(32));
                        realEstate.setHeating(rs.getString(33));
                        realEstate.setId(new Integer(rs.getInt(34)));
                        realEstate.setIdCustomer(new Integer(rs.getInt(35)));
                        realEstate.setKey(rs.getString(36));
                        realEstate.setKitchen(rs.getString(37));
                        realEstate.setLastUpdate(rs.getString(38));
                        realEstate.setLastUser(rs.getString(39));
                        realEstate.setLifeannuity(rs.getString(40));
                        realEstate.setLivingroom(rs.getString(41));
                        realEstate.setLoggia(rs.getString(42));
                        realEstate.setMezzanine(rs.getString(43));
                        realEstate.setNumConsultCC(new Integer(rs.getInt(44)));
                        realEstate.setNumConsultWeb(new Integer(rs.getInt(45)));
                        realEstate.setObservation(rs.getString(46));
                        realEstate.setOption(rs.getString(47));
                        realEstate.setOptionFisc(rs.getString(48));
                        realEstate.setPanel(rs.getString(49));
                        realEstate.setParking(rs.getString(50));
                        realEstate.setPhoto1(rs.getBytes(51));
                        realEstate.setPhoto2(rs.getBytes(52));
                        realEstate.setPhoto3(rs.getBytes(53));
                        realEstate.setPhoto4(rs.getBytes(54));
                        realEstate.setPhoto5(rs.getBytes(55));
                        realEstate.setPhoto6(rs.getBytes(56));
                        realEstate.setPhoto7(rs.getBytes(57));
                        realEstate.setPhoto8(rs.getBytes(58));
                        realEstate.setPhoto9(rs.getBytes(59));
                        realEstate.setPhoto10(rs.getBytes(60));
                        realEstate.setPhotosValid(rs.getString(61));
                        realEstate.setPool(rs.getString(62));
                        realEstate.setPlot(rs.getString(63));
                        realEstate.setPrintObservation(rs.getString(64));
                        realEstate.setRef(new Integer(rs.getInt(65)));
                        realEstate.setRefInternal(rs.getString(66));
                        realEstate.setRent(rs.getBigDecimal(67));
                        realEstate.setReserve(rs.getString(68));
                        realEstate.setRestroom(rs.getString(69));
                        realEstate.setRoofgarden(rs.getString(70));
                        realEstate.setSale(rs.getBigDecimal(71));
                        realEstate.setSector(rs.getString(72));
                        realEstate.setShower(rs.getString(73));
                        realEstate.setSort(rs.getString(74));
                        realEstate.setStanding(rs.getString(75));
                        realEstate.setState(rs.getString(76));
                        realEstate.setStylefloor(rs.getString(77));
                        realEstate.setSurfaceFloor(new Integer(rs.getInt(78)));
                        realEstate.setSurfaceGround(new Integer(rs.getInt(79)));
                        realEstate.setTv(rs.getString(80));
                        realEstate.setType(rs.getString(81));
                        realEstate.setVacation(rs.getString(82));
                        realEstate.setWeb(rs.getString(83));
                        realEstate.setZip(rs.getString(84));
                        realEstate.setAgencyId(new Integer(rs.getInt(85)));
                        realEstate.setExitCustomer(rs.getString(86));
                        realEstate.setSGarage(new Integer(rs.getInt(87)));
                        realEstate.setSLivingroom(new Integer(rs.getInt(88)));
                        realEstate.setSHall(new Integer(rs.getInt(89)));
                        realEstate.setSRoofgarden(new Integer(rs.getInt(90)));
                        realEstate.setSMezzanine(new Integer(rs.getInt(91)));
                        realEstate.setSLoggia(new Integer(rs.getInt(92)));
                        realEstate.setSRoom(new Integer(rs.getInt(93)));
                        realEstate.setSCellar(new Integer(rs.getInt(94)));
                        realEstate.setCustomer(rs.getString(95));
                        realEstate.setResidance(rs.getString(96));
                        realEstate.setSKitchen(new Integer(rs.getInt(97)));
                        realEstate.setSBalcony(new Integer(rs.getInt(98)));
                        realEstate.setCellarWine(rs.getString(99));
                        realEstate.setWalls(rs.getString(100));
                        realEstate.setDuplex(rs.getString(101));
                        realEstate.setSCellarWine(new Integer(rs.getInt(102)));
                        return realEstate;
                    } else {
                        return null;
                    }

                case 5: // '\005'
                    pst = con.prepareStatement("SELECT ID,GROUP_ID,USERNAME,PASS,COMPAGNY,STATUS,ADDR_LN1,ADDR_LN2,CITY,COUNTY,ZIP,TEL1,TEL2,FAX,EMAIL,WEB,ACCESS,WWW,WELCOME_EMAIL,WELCOME_TITLE,NEW_AD_EMAIL_UP,NEW_AD_EMAIL_DOWN,NEW_AD_TITLE,SMTP,EXPIRY_DATE,PAYMENT_DUE,COMMENT,LOGO,LAST_UPDATE,LAST_USER,SIRET,NAF FROM AGENCY WHERE ID=?");
                    pst.setInt(1, pk.intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        agency = new Agency();
                        agency.setId(new Integer(rs.getInt(1)));
                        agency.setGroupId(new Integer(rs.getInt(2)));
                        agency.setUsername(rs.getString(3));
                        agency.setPass(rs.getString(4));
                        agency.setCompagny(rs.getString(5));
                        agency.setStatus(rs.getString(6));
                        agency.setAddrLn1(rs.getString(7));
                        agency.setAddrLn2(rs.getString(8));
                        agency.setCity(rs.getString(9));
                        agency.setCounty(rs.getString(10));
                        agency.setZip(rs.getString(11));
                        agency.setTel1(rs.getString(12));
                        agency.setTel2(rs.getString(13));
                        agency.setFax(rs.getString(14));
                        agency.setEmail(rs.getString(15));
                        agency.setWeb(rs.getString(16));
                        agency.setAccess(new Integer(rs.getInt(17)));
                        agency.setWww(rs.getString(18));
                        agency.setWelcomeEmail(rs.getString(19));
                        agency.setWelcomeTitle(rs.getString(20));
                        agency.setNewAdEmailUp(rs.getString(21));
                        agency.setNewAdEmailDown(rs.getString(22));
                        agency.setNewAdTitle(rs.getString(23));
                        agency.setSmtp(rs.getString(24));
                        agency.setExpiryDate(rs.getString(25));
                        agency.setPaymentDue(rs.getBigDecimal(26));
                        agency.setComment(rs.getString(27));
                        agency.setLogo(rs.getBytes(28));
                        agency.setLastUpdate(rs.getString(29));
                        agency.setLastUser(rs.getString(30));
                        agency.setSiret(rs.getString(31));
                        agency.setNaf(rs.getString(32));
                        return agency;
                    } else {
                        return null;
                    }

                case 6: // '\006'
                    pst = con.prepareStatement("SELECT ID,NAME,ROLE,NOTE,LAST_UPDATE,LAST_USER FROM GROUPS WHERE ID=?");
                    pst.setInt(1, pk.intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        group = new Group();
                        group.setId(new Integer(rs.getInt(1)));
                        group.setName(rs.getString(2));
                        group.setRole(rs.getString(3));
                        group.setNote(rs.getString(4));
                        group.setLastUpdate(rs.getString(5));
                        group.setLastUser(rs.getString(6));
                        return group;
                    } else {
                        return null;
                    }

                case 7: // '\007'
                    pst = con.prepareStatement("SELECT ID,REALESTATE_ID,CUSTOMER_ID,BEGINDATE,ENDDATE,TAKEOPTION,CONFIRMED,LAST_UPDATE,LAST_USER,NOTE FROM AVAILABILITY WHERE ID=?");
                    pst.setInt(1, pk.intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        availability = new Availability();
                        availability.setId(new Integer(rs.getInt(1)));
                        availability.setRealEstateId(new Integer(rs.getInt(2)));
                        availability.setCustomerId(new Integer(rs.getInt(3)));
                        availability.setBeginDate(rs.getString(4));
                        availability.setEndDate(rs.getString(5));
                        availability.setOption(rs.getString(6));
                        availability.setConfirmed(rs.getString(7));
                        availability.setLastUpdate(rs.getString(8));
                        availability.setLastUser(rs.getString(9));
                        availability.setNotes(rs.getString(10));
                        return availability;
                    } else {
                        return null;
                    }

                case 8: // '\b'
                    pst = con.prepareStatement("SELECT ID,STATUS,FIRSTNAME,LASTNAME,ADDRLN1,ADDRLN2,ZIP,CITY,STATE,COUNTRY,TEL,CEL,FAX,EMAIL,WWW,BANK,BANKNUMBER,PAYMENTBY,CARDNAME,CARDNUMBER,CARDEXPIRYDATE,CARDDIGICODE,NOTES,LASTUSERID,LASTTIME,SALESPERSONID FROM CUSTOMER WHERE ID=?");
                    pst.setInt(1, pk.intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        customer = new Customer();
                        customer.setId(new Integer(rs.getInt(1)));
                        customer.setStatus(rs.getString(2));
                        customer.setFirstname(rs.getString(3));
                        customer.setLastname(rs.getString(4));
                        customer.setAddrLn1(rs.getString(5));
                        customer.setAddrLn2(rs.getString(6));
                        customer.setZip(rs.getString(7));
                        customer.setCity(rs.getString(8));
                        customer.setState(rs.getString(9));
                        customer.setCountry(rs.getString(10));
                        customer.setTel(rs.getString(11));
                        customer.setCel(rs.getString(12));
                        customer.setFax(rs.getString(13));
                        customer.setEmail(rs.getString(14));
                        customer.setWww(rs.getString(15));
                        customer.setBank(rs.getString(16));
                        customer.setBankNumber(rs.getString(17));
                        customer.setPaymentBy(rs.getString(18));
                        customer.setCardName(rs.getString(19));
                        customer.setCardNumber(rs.getString(20));
                        customer.setCardExpiryDate(rs.getString(21));
                        customer.setCardDigiCode(rs.getString(22));
                        customer.setNotes(rs.getString(23));
                        customer.setLastUserId(rs.getString(24));
                        customer.setLastTime(rs.getString(25));
                        customer.setSalesPersonId(new Integer(rs.getInt(26)));
                        return customer;
                    } else {
                        return null;
                    }

                case 9: // '\t'
                    pst = con.prepareStatement("SELECT ID,LASTNAME,FIRSTNAME,AREA,TEL,CEL,FAX,EMAIL,LEVEL,SALESRATE,TOTALSALESRATE,RESULTS,RESULTSDATE,OBJECTIF,OBJECTIFDATE,NOTES,LASTUSERID,LASTTIME,SALESPERSONFAMILY,ADDRLN1,ADDRLN2,ZIP,CITY,COUNTRY FROM SALESPERSON WHERE ID=?");
                    pst.setInt(1, pk.intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        salesPerson = new SalesPerson();
                        salesPerson.setId(new Integer(rs.getInt(1)));
                        salesPerson.setLastname(rs.getString(2));
                        salesPerson.setFirstname(rs.getString(3));
                        salesPerson.setArea(rs.getString(4));
                        salesPerson.setTel(rs.getString(5));
                        salesPerson.setCel(rs.getString(6));
                        salesPerson.setFax(rs.getString(7));
                        salesPerson.setEmail(rs.getString(8));
                        salesPerson.setLevel(rs.getString(9));
                        salesPerson.setSalesRate(rs.getBigDecimal(10));
                        salesPerson.setTotalSalesRate(rs.getBigDecimal(11));
                        salesPerson.setResults(rs.getBigDecimal(12));
                        salesPerson.setResultsDate(date.toWI(rs.getString(13)));
                        salesPerson.setObjectif(rs.getBigDecimal(14));
                        salesPerson.setObjectifDate(date.toWI(rs.getString(15)));
                        salesPerson.setNotes(rs.getString(16));
                        salesPerson.setLastUserId(rs.getString(17));
                        salesPerson.setLastTime(rs.getString(18));
                        salesPerson.setSalesPersonFamily(rs.getString(19));
                        salesPerson.setAddrLn1(rs.getString(20));
                        salesPerson.setAddrLn2(rs.getString(21));
                        salesPerson.setZip(rs.getString(22));
                        salesPerson.setCity(rs.getString(23));
                        salesPerson.setCountry(rs.getString(24));
                        return salesPerson;
                    } else {
                        return null;
                    }

                case 2: // '\002'
                case 3: // '\003'
                default:
                    return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private Collection readSQLAll(Integer pk, int type) {
        ArrayList list = new ArrayList();
        try {
            switch (type) {
                case 1: // '\001'
                    pst = con.prepareStatement("SELECT ID,GROUP_ID,USERNAME,PASS,LASTNAME,FIRSTNAME,ADDR_LN1,ADDR_LN2,CITY,COUNTY,ZIP,TEL1,TEL2,FAX,EMAIL,WEB,AGENCY_ID,ACCESS,EXPIRY_DATE,PAYMENT_DUE,COMMENT,LOGO,LAST_UPDATE,LAST_USER FROM USER WHERE GROUP_ID=?");
                    pst.setInt(1, pk.intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        do {
                            user = new User();
                            user.setId(new Integer(rs.getInt(1)));
                            user.setGroupId(new Integer(rs.getInt(2)));
                            user.setUsername(rs.getString(3));
                            user.setPass(rs.getString(4));
                            user.setLastname(rs.getString(5));
                            user.setFirstname(rs.getString(6));
                            user.setAddrLn1(rs.getString(7));
                            user.setAddrLn2(rs.getString(8));
                            user.setCity(rs.getString(9));
                            user.setCounty(rs.getString(10));
                            user.setZip(rs.getString(11));
                            user.setTel1(rs.getString(12));
                            user.setTel2(rs.getString(13));
                            user.setFax(rs.getString(14));
                            user.setEmail(rs.getString(15));
                            user.setWeb(rs.getString(16));
                            user.setAgencyId(new Integer(rs.getInt(17)));
                            user.setAccess(new Integer(rs.getInt(18)));
                            user.setExpiryDate(rs.getString(19));
                            user.setPaymentDue(rs.getBigDecimal(20));
                            user.setComment(rs.getString(21));
                            user.setLogo(rs.getBytes(22));
                            user.setLastUpdate(rs.getString(23));
                            user.setLastUser(rs.getString(24));
                            list.add(user);
                        } while (rs.next());
                        return list;
                    } else {
                        return null;
                    }

                case 4: // '\004'
                    pst = con.prepareStatement("SELECT ADDR_LN1,ADDR_LN2,AGENCY_FEES,ARCHIVE,AREA,ASPECT,AVAILABILITY,BALCONY,BATCH,BATHROOM,BED,BEDROOM,CATEGORY,CELLAR,CHARGE,CHARGE_DEPOSIT,CONDITIONS,COUNTY,COUNTRY,CREATEDATE,CUPBOARD,DEPARTURE,DEPARTURE_CUSTOMER,DEPOSIT,DESCRIPTION,ELEVATOR,FIREPLACE,FLOOR,FURNISHED,GARAGE,GARDEN,HALL,HEATING,ID,ID_CUSTOMER,BUNCHKEY,KITCHEN,LAST_UPDATE,LAST_USER,LIFEANNUITY,LIVINGROOM,LOGGIA,MEZZANINE,NUM_CONSULT_CC,NUM_CONSULT_WEB,OBSERVATION,ONLYOPTION,OPTION_FISC,PANEL,PARKING,PHOTO1,PHOTO2,PHOTO3,PHOTO4,PHOTO5,PHOTO6,PHOTO7,PHOTO8,PHOTO9,PHOTO10,PHOTOS_VALID,POOL,PLOT,PRINT_OBSERVATION,REF,REF_INTERNAL,RENT,RESERVE,RESTROOM,ROOFGARDEN,SALE,SECTOR,SHOWER,SORT,STANDING,STATE,STYLEFLOOR,SURFACE_FLOOR,SURFACE_GROUND,TV,TYPE,VACATION,WEB,ZIP,AGENCY_ID,EXIT_CUSTOMER,S_GARAGE,S_LIVINGROOM,S_HALL,S_ROOFGARDEN,S_MEZZANINE,S_LOGGIA,S_ROOM,S_CELLAR,CUSTOMER,RESIDANCE,S_KITCHEN,S_BALCONY,CELLAR_WINE,WALLS,DUPLEX,S_CELLAR_WINE FROM REALESTATE WHERE AGENCY_ID=?");
                    pst.setInt(1, pk.intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        do {
                            realEstate = new RealEstate();
                            realEstate.setAddrLn1(rs.getString(1));
                            realEstate.setAddrLn2(rs.getString(2));
                            realEstate.setAgencyFees(rs.getBigDecimal(3));
                            realEstate.setArchive(rs.getString(4));
                            realEstate.setArea(rs.getString(5));
                            realEstate.setAspect(rs.getString(6));
                            realEstate.setAvailability(rs.getString(7));
                            realEstate.setBalcony(rs.getString(8));
                            realEstate.setBatch(rs.getString(9));
                            realEstate.setBathroom(rs.getString(10));
                            realEstate.setBed(rs.getString(11));
                            realEstate.setBedroom(rs.getString(12));
                            realEstate.setCategory(rs.getString(13));
                            realEstate.setCellar(rs.getString(14));
                            realEstate.setCharge(rs.getBigDecimal(15));
                            realEstate.setChargeDeposit(rs.getBigDecimal(16));
                            realEstate.setCondition(rs.getString(17));
                            realEstate.setCounty(rs.getString(18));
                            realEstate.setCountry(rs.getString(19));
                            realEstate.setCreate(rs.getString(20));
                            realEstate.setCupboard(rs.getString(21));
                            realEstate.setDeparture(rs.getString(22));
                            realEstate.setDepartureCustomer(rs.getString(23));
                            realEstate.setDeposit(rs.getBigDecimal(24));
                            realEstate.setDescription(rs.getString(25));
                            realEstate.setElevator(rs.getString(26));
                            realEstate.setFireplace(rs.getString(27));
                            realEstate.setFloor(rs.getString(28));
                            realEstate.setFurnished(rs.getString(29));
                            realEstate.setGarage(rs.getString(30));
                            realEstate.setGarden(rs.getString(31));
                            realEstate.setHall(rs.getString(32));
                            realEstate.setHeating(rs.getString(33));
                            realEstate.setId(new Integer(rs.getInt(34)));
                            realEstate.setIdCustomer(new Integer(rs.getInt(35)));
                            realEstate.setKey(rs.getString(36));
                            realEstate.setKitchen(rs.getString(37));
                            realEstate.setLastUpdate(rs.getString(38));
                            realEstate.setLastUser(rs.getString(39));
                            realEstate.setLifeannuity(rs.getString(40));
                            realEstate.setLivingroom(rs.getString(41));
                            realEstate.setLoggia(rs.getString(42));
                            realEstate.setMezzanine(rs.getString(43));
                            realEstate.setNumConsultCC(new Integer(rs.getInt(44)));
                            realEstate.setNumConsultWeb(new Integer(rs.getInt(45)));
                            realEstate.setObservation(rs.getString(46));
                            realEstate.setOption(rs.getString(47));
                            realEstate.setOptionFisc(rs.getString(48));
                            realEstate.setPanel(rs.getString(49));
                            realEstate.setParking(rs.getString(50));
                            realEstate.setPhoto1(rs.getBytes(51));
                            realEstate.setPhoto2(rs.getBytes(52));
                            realEstate.setPhoto3(rs.getBytes(53));
                            realEstate.setPhoto4(rs.getBytes(54));
                            realEstate.setPhoto5(rs.getBytes(55));
                            realEstate.setPhoto6(rs.getBytes(56));
                            realEstate.setPhoto7(rs.getBytes(57));
                            realEstate.setPhoto8(rs.getBytes(58));
                            realEstate.setPhoto9(rs.getBytes(59));
                            realEstate.setPhoto10(rs.getBytes(60));
                            realEstate.setPhotosValid(rs.getString(61));
                            realEstate.setPool(rs.getString(62));
                            realEstate.setPlot(rs.getString(63));
                            realEstate.setPrintObservation(rs.getString(64));
                            realEstate.setRef(new Integer(rs.getInt(65)));
                            realEstate.setRefInternal(rs.getString(66));
                            realEstate.setRent(rs.getBigDecimal(67));
                            realEstate.setReserve(rs.getString(68));
                            realEstate.setRestroom(rs.getString(69));
                            realEstate.setRoofgarden(rs.getString(70));
                            realEstate.setSale(rs.getBigDecimal(71));
                            realEstate.setSector(rs.getString(72));
                            realEstate.setShower(rs.getString(73));
                            realEstate.setSort(rs.getString(74));
                            realEstate.setStanding(rs.getString(75));
                            realEstate.setState(rs.getString(76));
                            realEstate.setStylefloor(rs.getString(77));
                            realEstate.setSurfaceFloor(new Integer(rs.getInt(78)));
                            realEstate.setSurfaceGround(new Integer(rs.getInt(79)));
                            realEstate.setTv(rs.getString(80));
                            realEstate.setType(rs.getString(81));
                            realEstate.setVacation(rs.getString(82));
                            realEstate.setWeb(rs.getString(83));
                            realEstate.setZip(rs.getString(84));
                            realEstate.setAgencyId(new Integer(rs.getInt(85)));
                            realEstate.setExitCustomer(rs.getString(86));
                            realEstate.setSGarage(new Integer(rs.getInt(87)));
                            realEstate.setSLivingroom(new Integer(rs.getInt(88)));
                            realEstate.setSHall(new Integer(rs.getInt(89)));
                            realEstate.setSRoofgarden(new Integer(rs.getInt(90)));
                            realEstate.setSMezzanine(new Integer(rs.getInt(91)));
                            realEstate.setSLoggia(new Integer(rs.getInt(92)));
                            realEstate.setSRoom(new Integer(rs.getInt(93)));
                            realEstate.setSCellar(new Integer(rs.getInt(94)));
                            realEstate.setCustomer(rs.getString(95));
                            realEstate.setResidance(rs.getString(96));
                            realEstate.setSKitchen(new Integer(rs.getInt(97)));
                            realEstate.setSBalcony(new Integer(rs.getInt(98)));
                            realEstate.setCellarWine(rs.getString(99));
                            realEstate.setWalls(rs.getString(100));
                            realEstate.setDuplex(rs.getString(101));
                            realEstate.setSCellarWine(new Integer(rs.getInt(102)));
                            list.add(realEstate);
                        } while (rs.next());
                        return list;
                    } else {
                        return null;
                    }

                case 5: // '\005'
                    pst = con.prepareStatement("SELECT ID,GROUP_ID,USERNAME,PASS,COMPAGNY,STATUS,ADDR_LN1,ADDR_LN2,CITY,COUNTY,ZIP,TEL1,TEL2,FAX,EMAIL,WEB,ACCESS,WWW,WELCOME_EMAIL,WELCOME_TITLE,NEW_AD_EMAIL_UP,NEW_AD_EMAIL_DOWN,NEW_AD_TITLE,SMTP,EXPIRY_DATE,PAYMENT_DUE,COMMENT,LOGO,LAST_UPDATE,LAST_USER,SIRET,NAF FROM AGENCY");
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        do {
                            agency = new Agency();
                            agency.setId(new Integer(rs.getInt(1)));
                            agency.setGroupId(new Integer(rs.getInt(2)));
                            agency.setUsername(rs.getString(3));
                            agency.setPass(rs.getString(4));
                            agency.setCompagny(rs.getString(5));
                            agency.setStatus(rs.getString(6));
                            agency.setAddrLn1(rs.getString(7));
                            agency.setAddrLn2(rs.getString(8));
                            agency.setCity(rs.getString(9));
                            agency.setCounty(rs.getString(10));
                            agency.setZip(rs.getString(11));
                            agency.setTel1(rs.getString(12));
                            agency.setTel2(rs.getString(13));
                            agency.setFax(rs.getString(14));
                            agency.setEmail(rs.getString(15));
                            agency.setWeb(rs.getString(16));
                            agency.setAccess(new Integer(rs.getInt(17)));
                            agency.setWww(rs.getString(18));
                            agency.setWelcomeEmail(rs.getString(19));
                            agency.setWelcomeTitle(rs.getString(20));
                            agency.setNewAdEmailUp(rs.getString(21));
                            agency.setNewAdEmailDown(rs.getString(22));
                            agency.setNewAdTitle(rs.getString(23));
                            agency.setSmtp(rs.getString(24));
                            agency.setExpiryDate(rs.getString(25));
                            agency.setPaymentDue(rs.getBigDecimal(26));
                            agency.setComment(rs.getString(27));
                            agency.setLogo(rs.getBytes(28));
                            agency.setLastUpdate(rs.getString(29));
                            agency.setLastUser(rs.getString(30));
                            agency.setSiret(rs.getString(31));
                            agency.setNaf(rs.getString(32));
                            list.add(agency);
                        } while (rs.next());
                        return list;
                    } else {
                        return null;
                    }

                case 6: // '\006'
                    pst = con.prepareStatement("SELECT ID,NAME,ROLE,NOTE,LAST_UPDATE,LAST_USER FROM GROUPS");
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        do {
                            group = new Group();
                            group.setId(new Integer(rs.getInt(1)));
                            group.setName(rs.getString(2));
                            group.setRole(rs.getString(3));
                            group.setNote(rs.getString(4));
                            group.setLastUpdate(rs.getString(5));
                            group.setLastUser(rs.getString(6));
                            list.add(group);
                        } while (rs.next());
                        return list;
                    } else {
                        return null;
                    }

                case 7: // '\007'
                    pst = con.prepareStatement("SELECT ID,REALESTATE_ID,CUSTOMER_ID,BEGINDATE,ENDDATE,TAKEOPTION,CONFIRMED,LAST_UPDATE,LAST_USER,NOTE FROM AVAILABILITY WHERE REALESTATE_ID=?");
                    pst.setInt(1, pk.intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        do {
                            availability = new Availability();
                            availability.setId(new Integer(rs.getInt(1)));
                            availability.setRealEstateId(new Integer(rs.getInt(2)));
                            availability.setCustomerId(new Integer(rs.getInt(3)));
                            availability.setBeginDate(rs.getString(4));
                            availability.setEndDate(rs.getString(5));
                            availability.setOption(rs.getString(6));
                            availability.setConfirmed(rs.getString(7));
                            availability.setLastUpdate(rs.getString(8));
                            availability.setLastUser(rs.getString(9));
                            availability.setNotes(rs.getString(10));
                            list.add(availability);
                        } while (rs.next());
                        return list;
                    } else {
                        return null;
                    }

                case 8: // '\b'
                    pst = con.prepareStatement("SELECT ID,STATUS,FIRSTNAME,LASTNAME,ADDRLN1,ADDRLN2,ZIP,CITY,STATE,COUNTRY,TEL,CEL,FAX,EMAIL,WWW,BANK,BANKNUMBER,PAYMENTBY,CARDNAME,CARDNUMBER,CARDEXPIRYDATE,CARDDIGICODE,NOTES,LASTUSERID,LASTTIME,SALESPERSONID FROM CUSTOMER WHERE SALESPERSONID=?");
                    pst.setInt(1, pk.intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        do {
                            customer = new Customer();
                            customer.setId(new Integer(rs.getInt(1)));
                            customer.setStatus(rs.getString(2));
                            customer.setFirstname(rs.getString(3));
                            customer.setLastname(rs.getString(4));
                            customer.setAddrLn1(rs.getString(5));
                            customer.setAddrLn2(rs.getString(6));
                            customer.setZip(rs.getString(7));
                            customer.setCity(rs.getString(8));
                            customer.setState(rs.getString(9));
                            customer.setCountry(rs.getString(10));
                            customer.setTel(rs.getString(11));
                            customer.setCel(rs.getString(12));
                            customer.setFax(rs.getString(13));
                            customer.setEmail(rs.getString(14));
                            customer.setWww(rs.getString(15));
                            customer.setBank(rs.getString(16));
                            customer.setBankNumber(rs.getString(17));
                            customer.setPaymentBy(rs.getString(18));
                            customer.setCardName(rs.getString(19));
                            customer.setCardNumber(rs.getString(20));
                            customer.setCardExpiryDate(rs.getString(21));
                            customer.setCardDigiCode(rs.getString(22));
                            customer.setNotes(rs.getString(23));
                            customer.setLastUserId(rs.getString(24));
                            customer.setLastTime(rs.getString(25));
                            customer.setSalesPersonId(new Integer(rs.getInt(26)));
                            list.add(customer);
                        } while (rs.next());
                        return list;
                    } else {
                        return null;
                    }

                case 9: // '\t'
                    pst = con.prepareStatement("SELECT ID,LASTNAME,FIRSTNAME,AREA,TEL,CEL,FAX,EMAIL,LEVEL,SALESRATE,TOTALSALESRATE,RESULTS,RESULTSDATE,OBJECTIF,OBJECTIFDATE,NOTES,LASTUSERID,LASTTIME,SALESPERSONFAMILY,ADDRLN1,ADDRLN2,ZIP,CITY,COUNTRY FROM SALESPERSON ORDER BY LASTNAME");
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        do {
                            salesPerson = new SalesPerson();
                            salesPerson.setId(new Integer(rs.getInt(1)));
                            salesPerson.setLastname(rs.getString(2));
                            salesPerson.setFirstname(rs.getString(3));
                            salesPerson.setArea(rs.getString(4));
                            salesPerson.setTel(rs.getString(5));
                            salesPerson.setCel(rs.getString(6));
                            salesPerson.setFax(rs.getString(7));
                            salesPerson.setEmail(rs.getString(8));
                            salesPerson.setLevel(rs.getString(9));
                            salesPerson.setSalesRate(rs.getBigDecimal(10));
                            salesPerson.setTotalSalesRate(rs.getBigDecimal(11));
                            salesPerson.setResults(rs.getBigDecimal(12));
                            salesPerson.setResultsDate(date.toWI(rs.getString(13)));
                            salesPerson.setObjectif(rs.getBigDecimal(14));
                            salesPerson.setObjectifDate(date.toWI(rs.getString(15)));
                            salesPerson.setNotes(rs.getString(16));
                            salesPerson.setLastUserId(rs.getString(17));
                            salesPerson.setLastTime(rs.getString(18));
                            salesPerson.setSalesPersonFamily(rs.getString(19));
                            salesPerson.setAddrLn1(rs.getString(20));
                            salesPerson.setAddrLn2(rs.getString(21));
                            salesPerson.setZip(rs.getString(22));
                            salesPerson.setCity(rs.getString(23));
                            salesPerson.setCountry(rs.getString(24));
                            list.add(salesPerson);
                        } while (rs.next());
                        return list;
                    } else {
                        return null;
                    }

                case 2: // '\002'
                case 3: // '\003'
                default:
                    return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private Object readSQLBy(String arg0, String arg1, int type) {
        try {
            switch (type) {
                case 1: // '\001'
                    pst = con.prepareStatement("SELECT ID,GROUP_ID,USERNAME,PASS,LASTNAME,FIRSTNAME,ADDR_LN1,ADDR_LN2,CITY,COUNTY,ZIP,TEL1,TEL2,FAX,EMAIL,WEB,AGENCY_ID,ACCESS,EXPIRY_DATE,PAYMENT_DUE,COMMENT,LOGO,LAST_UPDATE,LAST_USER FROM USER WHERE USERNAME=? AND PASS=?");
                    pst.setString(1, arg0);
                    pst.setString(2, arg1);
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        user = new User();
                        user.setId(new Integer(rs.getInt(1)));
                        user.setGroupId(new Integer(rs.getInt(2)));
                        user.setUsername(rs.getString(3));
                        user.setPass(rs.getString(4));
                        user.setLastname(rs.getString(5));
                        user.setFirstname(rs.getString(6));
                        user.setAddrLn1(rs.getString(7));
                        user.setAddrLn2(rs.getString(8));
                        user.setCity(rs.getString(9));
                        user.setCounty(rs.getString(10));
                        user.setZip(rs.getString(11));
                        user.setTel1(rs.getString(12));
                        user.setTel2(rs.getString(13));
                        user.setFax(rs.getString(14));
                        user.setEmail(rs.getString(15));
                        user.setWeb(rs.getString(16));
                        user.setAgencyId(new Integer(rs.getInt(17)));
                        user.setAccess(new Integer(rs.getInt(18)));
                        user.setExpiryDate(rs.getString(19));
                        user.setPaymentDue(rs.getBigDecimal(20));
                        user.setComment(rs.getString(21));
                        user.setLogo(rs.getBytes(22));
                        user.setLastUpdate(rs.getString(23));
                        user.setLastUser(rs.getString(24));
                        return user;
                    } else {
                        return null;
                    }

                case 4: // '\004'
                    pst = con.prepareStatement("SELECT ADDR_LN1,ADDR_LN2,AGENCY_FEES,ARCHIVE,AREA,ASPECT,AVAILABILITY,BALCONY,BATCH,BATHROOM,BED,BEDROOM,CATEGORY,CELLAR,CHARGE,CHARGE_DEPOSIT,CONDITIONS,COUNTY,COUNTRY,CREATEDATE,CUPBOARD,DEPARTURE,DEPARTURE_CUSTOMER,DEPOSIT,DESCRIPTION,ELEVATOR,FIREPLACE,FLOOR,FURNISHED,GARAGE,GARDEN,HALL,HEATING,ID,ID_CUSTOMER,BUNCHKEY,KITCHEN,LAST_UPDATE,LAST_USER,LIFEANNUITY,LIVINGROOM,LOGGIA,MEZZANINE,NUM_CONSULT_CC,NUM_CONSULT_WEB,OBSERVATION,ONLYOPTION,OPTION_FISC,PANEL,PARKING,PHOTO1,PHOTO2,PHOTO3,PHOTO4,PHOTO5,PHOTO6,PHOTO7,PHOTO8,PHOTO9,PHOTO10,PHOTOS_VALID,POOL,PLOT,PRINT_OBSERVATION,REF,REF_INTERNAL,RENT,RESERVE,RESTROOM,ROOFGARDEN,SALE,SECTOR,SHOWER,SORT,STANDING,STATE,STYLEFLOOR,SURFACE_FLOOR,SURFACE_GROUND,TV,TYPE,VACATION,WEB,ZIP,AGENCY_ID,EXIT_CUSTOMER,S_GARAGE,S_LIVINGROOM,S_HALL,S_ROOFGARDEN,S_MEZZANINE,S_LOGGIA,S_ROOM,S_CELLAR,CUSTOMER,RESIDANCE,S_KITCHEN,S_BALCONY,CELLAR_WINE,WALLS,DUPLEX,S_CELLAR_WINE FROM REALESTATE WHERE REF_INTERNAL=? AND AGENCY_ID=?");
                    pst.setString(1, arg0);
                    pst.setInt(2, (new Integer(arg1)).intValue());
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        realEstate = new RealEstate();
                        realEstate.setAddrLn1(rs.getString(1));
                        realEstate.setAddrLn2(rs.getString(2));
                        realEstate.setAgencyFees(rs.getBigDecimal(3));
                        realEstate.setArchive(rs.getString(4));
                        realEstate.setArea(rs.getString(5));
                        realEstate.setAspect(rs.getString(6));
                        realEstate.setAvailability(rs.getString(7));
                        realEstate.setBalcony(rs.getString(8));
                        realEstate.setBatch(rs.getString(9));
                        realEstate.setBathroom(rs.getString(10));
                        realEstate.setBed(rs.getString(11));
                        realEstate.setBedroom(rs.getString(12));
                        realEstate.setCategory(rs.getString(13));
                        realEstate.setCellar(rs.getString(14));
                        realEstate.setCharge(rs.getBigDecimal(15));
                        realEstate.setChargeDeposit(rs.getBigDecimal(16));
                        realEstate.setCondition(rs.getString(17));
                        realEstate.setCountry(rs.getString(18));
                        realEstate.setCounty(rs.getString(19));
                        realEstate.setCreate(rs.getString(20));
                        realEstate.setCupboard(rs.getString(21));
                        realEstate.setDeparture(rs.getString(22));
                        realEstate.setDepartureCustomer(rs.getString(23));
                        realEstate.setDeposit(rs.getBigDecimal(24));
                        realEstate.setDescription(rs.getString(25));
                        realEstate.setElevator(rs.getString(26));
                        realEstate.setFireplace(rs.getString(27));
                        realEstate.setFloor(rs.getString(28));
                        realEstate.setFurnished(rs.getString(29));
                        realEstate.setGarage(rs.getString(30));
                        realEstate.setGarden(rs.getString(31));
                        realEstate.setHall(rs.getString(32));
                        realEstate.setHeating(rs.getString(33));
                        realEstate.setId(new Integer(rs.getInt(34)));
                        realEstate.setIdCustomer(new Integer(rs.getInt(35)));
                        realEstate.setKey(rs.getString(36));
                        realEstate.setKitchen(rs.getString(37));
                        realEstate.setLastUpdate(rs.getString(38));
                        realEstate.setLastUser(rs.getString(39));
                        realEstate.setLifeannuity(rs.getString(40));
                        realEstate.setLivingroom(rs.getString(41));
                        realEstate.setLoggia(rs.getString(42));
                        realEstate.setMezzanine(rs.getString(43));
                        realEstate.setNumConsultCC(new Integer(rs.getInt(44)));
                        realEstate.setNumConsultWeb(new Integer(rs.getInt(45)));
                        realEstate.setObservation(rs.getString(46));
                        realEstate.setOption(rs.getString(47));
                        realEstate.setOptionFisc(rs.getString(48));
                        realEstate.setPanel(rs.getString(49));
                        realEstate.setParking(rs.getString(50));
                        realEstate.setPhoto1(rs.getBytes(51));
                        realEstate.setPhoto2(rs.getBytes(52));
                        realEstate.setPhoto3(rs.getBytes(53));
                        realEstate.setPhoto4(rs.getBytes(54));
                        realEstate.setPhoto5(rs.getBytes(55));
                        realEstate.setPhoto6(rs.getBytes(56));
                        realEstate.setPhoto7(rs.getBytes(57));
                        realEstate.setPhoto8(rs.getBytes(58));
                        realEstate.setPhoto9(rs.getBytes(59));
                        realEstate.setPhoto10(rs.getBytes(60));
                        realEstate.setPhotosValid(rs.getString(61));
                        realEstate.setPool(rs.getString(62));
                        realEstate.setPlot(rs.getString(63));
                        realEstate.setPrintObservation(rs.getString(64));
                        realEstate.setRef(new Integer(rs.getInt(65)));
                        realEstate.setRefInternal(rs.getString(66));
                        realEstate.setRent(rs.getBigDecimal(67));
                        realEstate.setReserve(rs.getString(68));
                        realEstate.setRestroom(rs.getString(69));
                        realEstate.setRoofgarden(rs.getString(70));
                        realEstate.setSale(rs.getBigDecimal(71));
                        realEstate.setSector(rs.getString(72));
                        realEstate.setShower(rs.getString(73));
                        realEstate.setSort(rs.getString(74));
                        realEstate.setStanding(rs.getString(75));
                        realEstate.setState(rs.getString(76));
                        realEstate.setStylefloor(rs.getString(77));
                        realEstate.setSurfaceFloor(new Integer(rs.getInt(78)));
                        realEstate.setSurfaceGround(new Integer(rs.getInt(79)));
                        realEstate.setTv(rs.getString(80));
                        realEstate.setType(rs.getString(81));
                        realEstate.setVacation(rs.getString(82));
                        realEstate.setWeb(rs.getString(83));
                        realEstate.setZip(rs.getString(84));
                        realEstate.setAgencyId(new Integer(rs.getInt(85)));
                        realEstate.setExitCustomer(rs.getString(86));
                        realEstate.setSGarage(new Integer(rs.getInt(87)));
                        realEstate.setSLivingroom(new Integer(rs.getInt(88)));
                        realEstate.setSHall(new Integer(rs.getInt(89)));
                        realEstate.setSRoofgarden(new Integer(rs.getInt(90)));
                        realEstate.setSMezzanine(new Integer(rs.getInt(91)));
                        realEstate.setSLoggia(new Integer(rs.getInt(92)));
                        realEstate.setSRoom(new Integer(rs.getInt(93)));
                        realEstate.setSCellar(new Integer(rs.getInt(94)));
                        realEstate.setCustomer(rs.getString(95));
                        realEstate.setResidance(rs.getString(96));
                        realEstate.setSKitchen(new Integer(rs.getInt(97)));
                        realEstate.setSBalcony(new Integer(rs.getInt(98)));
                        realEstate.setCellarWine(rs.getString(99));
                        realEstate.setWalls(rs.getString(100));
                        realEstate.setDuplex(rs.getString(101));
                        realEstate.setSCellarWine(new Integer(rs.getInt(102)));
                        return realEstate;
                    } else {
                        return null;
                    }

                case 5: // '\005'
                    pst = con.prepareStatement("SELECT ID,GROUP_ID,USERNAME,PASS,COMPAGNY,STATUS,ADDR_LN1,ADDR_LN2,CITY,COUNTY,ZIP,TEL1,TEL2,FAX,EMAIL,WEB,ACCESS,WWW,WELCOME_EMAIL,WELCOME_TITLE,NEW_AD_EMAIL_UP,NEW_AD_EMAIL_DOWN,NEW_AD_TITLE,SMTP,EXPIRY_DATE,PAYMENT_DUE,COMMENT,LOGO,LAST_UPDATE,LAST_USER,SIRET,NAF FROM AGENCY WHERE ID=? AND GROUP_ID=?");
                    pst.setString(1, arg0);
                    pst.setString(2, arg1);
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        agency = new Agency();
                        agency.setId(new Integer(rs.getInt(1)));
                        agency.setGroupId(new Integer(rs.getInt(2)));
                        agency.setUsername(rs.getString(3));
                        agency.setPass(rs.getString(4));
                        agency.setCompagny(rs.getString(5));
                        agency.setStatus(rs.getString(6));
                        agency.setAddrLn1(rs.getString(7));
                        agency.setAddrLn2(rs.getString(8));
                        agency.setCity(rs.getString(9));
                        agency.setCounty(rs.getString(10));
                        agency.setZip(rs.getString(11));
                        agency.setTel1(rs.getString(12));
                        agency.setTel2(rs.getString(13));
                        agency.setFax(rs.getString(14));
                        agency.setEmail(rs.getString(15));
                        agency.setWeb(rs.getString(16));
                        agency.setAccess(new Integer(rs.getInt(17)));
                        agency.setWww(rs.getString(18));
                        agency.setWelcomeEmail(rs.getString(19));
                        agency.setWelcomeTitle(rs.getString(20));
                        agency.setNewAdEmailUp(rs.getString(21));
                        agency.setNewAdEmailDown(rs.getString(22));
                        agency.setNewAdTitle(rs.getString(23));
                        agency.setSmtp(rs.getString(24));
                        agency.setExpiryDate(rs.getString(25));
                        agency.setPaymentDue(rs.getBigDecimal(26));
                        agency.setComment(rs.getString(27));
                        agency.setLogo(rs.getBytes(28));
                        agency.setLastUpdate(rs.getString(29));
                        agency.setLastUser(rs.getString(30));
                        agency.setSiret(rs.getString(31));
                        agency.setNaf(rs.getString(32));
                        return agency;
                    } else {
                        return null;
                    }

                case 6: // '\006'
                    pst = con.prepareStatement("SELECT ID,NAME,ROLE,NOTE,LAST_UPDATE,LAST_USER FROM GROUPS WHERE ID=? AND ROLE=?");
                    pst.setString(1, arg0);
                    pst.setString(2, arg1);
                    rs = pst.executeQuery();
                    if (rs.next()) {
                        group = new Group();
                        group.setId(new Integer(rs.getInt(1)));
                        group.setName(rs.getString(2));
                        group.setRole(rs.getString(3));
                        group.setNote(rs.getString(4));
                        group.setLastUpdate(rs.getString(5));
                        group.setLastUser(rs.getString(6));
                        return group;
                    } else {
                        return null;
                    }

                case 2: // '\002'
                case 3: // '\003'
                default:
                    return null;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    private boolean updateSQL(Object obj, int type) {
        try {
            switch (type) {
                case 1: // '\001'
                    user = (User) obj;
                    pst = con.prepareStatement("UPDATE USER SET ID=?,GROUP_ID=?,USERNAME=?,PASS=?,LASTNAME=?,FIRSTNAME=?,ADDR_LN1=?,ADDR_LN2=?,CITY=?,COUNTY=?,ZIP=?,TEL1=?,TEL2=?,FAX=?,EMAIL=?,WEB=?,AGENCY_ID=?,ACCESS=?,EXPIRY_DATE=?,PAYMENT_DUE=?,COMMENT=?,LOGO=?,LAST_UPDATE=?,LAST_USER=? WHERE ID=? AND LAST_UPDATE=?");
                    pst.setInt(1, user.getId().intValue());
                    pst.setInt(2, user.getGroupId().intValue());
                    pst.setString(3, user.getUsername());
                    pst.setString(4, user.getPass());
                    pst.setString(5, user.getLastname());
                    pst.setString(6, user.getFirstname());
                    pst.setString(7, user.getAddrLn1());
                    pst.setString(8, user.getAddrLn2());
                    pst.setString(9, user.getCity());
                    pst.setString(10, user.getCounty());
                    pst.setString(11, user.getZip());
                    pst.setString(12, user.getTel1());
                    pst.setString(13, user.getTel2());
                    pst.setString(14, user.getFax());
                    pst.setString(15, user.getEmail());
                    pst.setString(16, user.getWeb());
                    pst.setInt(17, user.getAgencyId().intValue());
                    pst.setInt(18, user.getAccess().intValue());
                    pst.setString(19, user.getExpiryDate());
                    pst.setBigDecimal(20, user.getPaymentDue());
                    pst.setString(21, user.getComment());
                    pst.setBytes(22, user.getLogo());
                    pst.setString(23, (new WIDate()).getDateTimeDB());
                    pst.setString(24, user.getLastUser());
                    pst.setInt(25, user.getId().intValue());
                    pst.setString(26, user.getLastUpdate());
                    pst.execute();
                    return pst.getUpdateCount() != 0;

                case 4: // '\004'
                    realEstate = (RealEstate) obj;
                    pst = con.prepareStatement("UPDATE REALESTATE SET ADDR_LN1=?,ADDR_LN2=?,AGENCY_FEES=?,ARCHIVE=?,AREA=?,ASPECT=?,AVAILABILITY=?,BALCONY=?,BATCH=?,BATHROOM=?,BED=?,BEDROOM=?,CATEGORY=?,CELLAR=?,CHARGE=?,CHARGE_DEPOSIT=?,CONDITIONS=?,COUNTY=?,COUNTRY=?,CREATEDATE=?,CUPBOARD=?,DEPARTURE=?,DEPARTURE_CUSTOMER=?,DEPOSIT=?,DESCRIPTION=?,ELEVATOR=?,FIREPLACE=?,FLOOR=?,FURNISHED=?,GARAGE=?,GARDEN=?,HALL=?,HEATING=?,ID=?,ID_CUSTOMER=?,BUNCHKEY=?,KITCHEN=?,LAST_UPDATE=?,LAST_USER=?,LIFEANNUITY=?,LIVINGROOM=?,LOGGIA=?,MEZZANINE=?,NUM_CONSULT_CC=?,NUM_CONSULT_WEB=?,OBSERVATION=?,ONLYOPTION=?,OPTION_FISC=?,PANEL=?,PARKING=?,PHOTO1=?,PHOTO2=?,PHOTO3=?,PHOTO4=?,PHOTO5=?,PHOTO6=?,PHOTO7=?,PHOTO8=?,PHOTO9=?,PHOTO10=?,PHOTOS_VALID=?,POOL=?,PLOT=?,PRINT_OBSERVATION=?,REF=?,REF_INTERNAL=?,RENT=?,RESERVE=?,RESTROOM=?,ROOFGARDEN=?,SALE=?,SECTOR=?,SHOWER=?,SORT=?,STANDING=?,STATE=?,STYLEFLOOR=?,SURFACE_FLOOR=?,SURFACE_GROUND=?,TV=?,TYPE=?,VACATION=?,WEB=?,ZIP=?,AGENCY_ID=?,EXIT_CUSTOMER=?,S_GARAGE=?,S_LIVINGROOM=?,S_HALL=?,S_ROOFGARDEN=?,S_MEZZANINE=?,S_LOGGIA=?,S_ROOM=?,S_CELLAR=?,CUSTOMER=?,RESIDANCE=?,S_KITCHEN=?,S_BALCONY=?,CELLAR_WINE=?,WALLS=?,DUPLEX=?,S_CELLAR_WINE=? WHERE ID=? AND LAST_UPDATE=?");
                    pst.setString(1, realEstate.getAddrLn1());
                    pst.setString(2, realEstate.getAddrLn2());
                    pst.setBigDecimal(3, realEstate.getAgencyFees());
                    pst.setString(4, realEstate.getArchive());
                    pst.setString(5, realEstate.getArea());
                    pst.setString(6, realEstate.getAspect());
                    pst.setString(7, realEstate.getAvailability());
                    pst.setString(8, realEstate.getBalcony());
                    pst.setString(9, realEstate.getBatch());
                    pst.setString(10, realEstate.getBathroom());
                    pst.setString(11, realEstate.getBed());
                    pst.setString(12, realEstate.getBedroom());
                    pst.setString(13, realEstate.getCategory());
                    pst.setString(14, realEstate.getCellar());
                    pst.setBigDecimal(15, realEstate.getCharge());
                    pst.setBigDecimal(16, realEstate.getChargeDeposit());
                    pst.setString(17, realEstate.getCondition());
                    pst.setString(18, realEstate.getCounty());
                    pst.setString(19, realEstate.getCountry());
                    pst.setString(20, realEstate.getCreate());
                    pst.setString(21, realEstate.getCupboard());
                    pst.setString(22, realEstate.getDeparture());
                    pst.setString(23, realEstate.getDepartureCustomer());
                    pst.setBigDecimal(24, realEstate.getDeposit());
                    pst.setString(25, realEstate.getDescription());
                    pst.setString(26, realEstate.getElevator());
                    pst.setString(27, realEstate.getFireplace());
                    pst.setString(28, realEstate.getFloor());
                    pst.setString(29, realEstate.getFurnished());
                    pst.setString(30, realEstate.getGarage());
                    pst.setString(31, realEstate.getGarden());
                    pst.setString(32, realEstate.getHall());
                    pst.setString(33, realEstate.getHeating());
                    pst.setInt(34, realEstate.getId().intValue());
                    pst.setInt(35, realEstate.getIdCustomer().intValue());
                    pst.setString(36, realEstate.getKey());
                    pst.setString(37, realEstate.getKitchen());
                    pst.setString(38, (new WIDate()).getDateTimeDB());
                    pst.setString(39, realEstate.getLastUser());
                    pst.setString(40, realEstate.getLifeannuity());
                    pst.setString(41, realEstate.getLivingroom());
                    pst.setString(42, realEstate.getLoggia());
                    pst.setString(43, realEstate.getMezzanine());
                    pst.setInt(44, realEstate.getNumConsultCC().intValue());
                    pst.setInt(45, realEstate.getNumConsultWeb().intValue());
                    pst.setString(46, realEstate.getObservation());
                    pst.setString(47, realEstate.getOption());
                    pst.setString(48, realEstate.getOptionFisc());
                    pst.setString(49, realEstate.getPanel());
                    pst.setString(50, realEstate.getParking());
                    pst.setBytes(51, realEstate.getPhoto1());
                    pst.setBytes(52, realEstate.getPhoto2());
                    pst.setBytes(53, realEstate.getPhoto3());
                    pst.setBytes(54, realEstate.getPhoto4());
                    pst.setBytes(55, realEstate.getPhoto5());
                    pst.setBytes(56, realEstate.getPhoto6());
                    pst.setBytes(57, realEstate.getPhoto7());
                    pst.setBytes(58, realEstate.getPhoto8());
                    pst.setBytes(59, realEstate.getPhoto9());
                    pst.setBytes(60, realEstate.getPhoto10());
                    pst.setString(61, realEstate.getPhotosValid());
                    pst.setString(62, realEstate.getPool());
                    pst.setString(63, realEstate.getPlot());
                    pst.setString(64, realEstate.getPrintObservation());
                    pst.setInt(65, realEstate.getRef().intValue());
                    pst.setString(66, realEstate.getRefInternal());
                    pst.setBigDecimal(67, realEstate.getRent());
                    pst.setString(68, realEstate.getReserve());
                    pst.setString(69, realEstate.getRestroom());
                    pst.setString(70, realEstate.getRoofgarden());
                    pst.setBigDecimal(71, realEstate.getSale());
                    pst.setString(72, realEstate.getSector());
                    pst.setString(73, realEstate.getShower());
                    pst.setString(74, realEstate.getSort());
                    pst.setString(75, realEstate.getStanding());
                    pst.setString(76, realEstate.getState());
                    pst.setString(77, realEstate.getStylefloor());
                    pst.setInt(78, realEstate.getSurfaceFloor().intValue());
                    pst.setInt(79, realEstate.getSurfaceGround().intValue());
                    pst.setString(80, realEstate.getTv());
                    pst.setString(81, realEstate.getType());
                    pst.setString(82, realEstate.getVacation());
                    pst.setString(83, realEstate.getWeb());
                    pst.setString(84, realEstate.getZip());
                    pst.setInt(85, realEstate.getAgencyId().intValue());
                    pst.setString(86, realEstate.getExitCustomer());
                    pst.setInt(87, realEstate.getSGarage().intValue());
                    pst.setInt(88, realEstate.getSLivingroom().intValue());
                    pst.setInt(89, realEstate.getSHall().intValue());
                    pst.setInt(90, realEstate.getSRoofgarden().intValue());
                    pst.setInt(91, realEstate.getSMezzanine().intValue());
                    pst.setInt(92, realEstate.getSLoggia().intValue());
                    pst.setInt(93, realEstate.getSRoom().intValue());
                    pst.setInt(94, realEstate.getSCellar().intValue());
                    pst.setString(95, realEstate.getCustomer());
                    pst.setString(96, realEstate.getResidance());
                    pst.setInt(97, realEstate.getSKitchen().intValue());
                    pst.setInt(98, realEstate.getSBalcony().intValue());
                    pst.setString(99, realEstate.getCellarWine());
                    pst.setString(100, realEstate.getWalls());
                    pst.setString(101, realEstate.getDuplex());
                    pst.setInt(102, realEstate.getSCellarWine().intValue());
                    pst.setInt(103, realEstate.getId().intValue());
                    pst.setString(104, realEstate.getLastUpdate());
                    pst.execute();
                    return pst.getUpdateCount() != 0;

                case 5: // '\005'
                    agency = (Agency) obj;
                    pst = con.prepareStatement("UPDATE AGENCY SET ID=?,GROUP_ID=?,USERNAME=?,PASS=?,COMPAGNY=?,STATUS=?,ADDR_LN1=?,ADDR_LN2=?,CITY=?,COUNTY=?,ZIP=?,TEL1=?,TEL2=?,FAX=?,EMAIL=?,WEB=?,ACCESS=?,WWW=?,WELCOME_EMAIL=?,WELCOME_TITLE=?,NEW_AD_EMAIL_UP=?,NEW_AD_EMAIL_DOWN=?,NEW_AD_TITLE=?,SMTP=?,EXPIRY_DATE=?,PAYMENT_DUE=?,COMMENT=?,LOGO=?,LAST_UPDATE=?,LAST_USER=?,SIRET=?,NAF=? WHERE ID=? AND LAST_UPDATE=?");
                    pst.setInt(1, agency.getId().intValue());
                    pst.setInt(2, agency.getGroupId().intValue());
                    pst.setString(3, agency.getUsername());
                    pst.setString(4, agency.getPass());
                    pst.setString(5, agency.getCompagny());
                    pst.setString(6, agency.getStatus());
                    pst.setString(7, agency.getAddrLn1());
                    pst.setString(8, agency.getAddrLn2());
                    pst.setString(9, agency.getCity());
                    pst.setString(10, agency.getCounty());
                    pst.setString(11, agency.getZip());
                    pst.setString(12, agency.getTel1());
                    pst.setString(13, agency.getTel2());
                    pst.setString(14, agency.getFax());
                    pst.setString(15, agency.getEmail());
                    pst.setString(16, agency.getWeb());
                    pst.setInt(17, agency.getAccess().intValue());
                    pst.setString(18, agency.getWww());
                    pst.setString(19, agency.getWelcomeEmail());
                    pst.setString(20, agency.getWelcomeTitle());
                    pst.setString(21, agency.getNewAdEmailUp());
                    pst.setString(22, agency.getNewAdEmailDown());
                    pst.setString(23, agency.getNewAdTitle());
                    pst.setString(24, agency.getSmtp());
                    pst.setString(25, agency.getExpiryDate());
                    pst.setBigDecimal(26, agency.getPaymentDue());
                    pst.setString(27, agency.getComment());
                    pst.setBytes(28, agency.getLogo());
                    pst.setString(29, (new WIDate()).getDateTimeDB());
                    pst.setString(30, agency.getLastUser());
                    pst.setString(31, agency.getSiret());
                    pst.setString(32, agency.getNaf());
                    pst.setInt(33, agency.getId().intValue());
                    pst.setString(34, agency.getLastUpdate());
                    pst.execute();
                    return pst.getUpdateCount() != 0;

                case 6: // '\006'
                    group = (Group) obj;
                    pst = con.prepareStatement("UPDATE GROUPS SET ID=?,NAME=?,ROLE=?,NOTE=?,LAST_UPDATE=?,LAST_USER=? WHERE ID=? AND LAST_UPDATE=?");
                    pst.setInt(1, group.getId().intValue());
                    pst.setString(2, group.getName());
                    pst.setString(3, group.getRole());
                    pst.setString(4, group.getNote());
                    pst.setString(5, (new WIDate()).getDateTimeDB());
                    pst.setString(6, group.getLastUser());
                    pst.setInt(7, group.getId().intValue());
                    pst.setString(8, group.getLastUpdate());
                    pst.execute();
                    return pst.getUpdateCount() != 0;

                case 7: // '\007'
                    availability = (Availability) obj;
                    pst = con.prepareStatement("UPDATE AVAILABILITY SET ID=?,REALESTATE_ID=?,CUSTOMER_ID=?,BEGINDATE=?,ENDDATE=?,TAKEOPTION=?,CONFIRMED=?,LAST_UPDATE=?,LAST_USER=?,NOTE=? WHERE ID=? AND LAST_UPDATE=?");
                    pst.setInt(1, availability.getId().intValue());
                    pst.setInt(2, availability.getRealEstateId().intValue());
                    pst.setInt(3, availability.getCustomerId().intValue());
                    pst.setString(4, availability.getBeginDate());
                    pst.setString(5, availability.getEndDate());
                    pst.setString(6, availability.getOption());
                    pst.setString(7, availability.getConfirmed());
                    pst.setString(8, (new WIDate()).getDateTimeDB());
                    pst.setString(9, availability.getLastUser());
                    pst.setString(10, availability.getNotes());
                    pst.setInt(11, availability.getId().intValue());
                    pst.setString(12, availability.getLastUpdate());
                    pst.execute();
                    return pst.getUpdateCount() != 0;

                case 8: // '\b'
                    customer = (Customer) obj;
                    pst = con.prepareStatement("UPDATE CUSTOMER SET ID=?,STATUS=?,FIRSTNAME=?,LASTNAME=?,ADDRLN1=?,ADDRLN2=?,ZIP=?,CITY=?,STATE=?,COUNTRY=?,TEL=?,CEL=?,FAX=?,EMAIL=?,WWW=?,BANK=?,BANKNUMBER=?,PAYMENTBY=?,CARDNAME=?,CARDNUMBER=?,CARDEXPIRYDATE=?,CARDDIGICODE=?,NOTES=?,LASTUSERID=?,LASTTIME=?,SALESPERSONID=? WHERE ID=? AND LASTTIME=?");
                    pst.setInt(1, customer.getId().intValue());
                    pst.setString(2, customer.getStatus());
                    pst.setString(3, customer.getFirstname());
                    pst.setString(4, customer.getLastname());
                    pst.setString(5, customer.getAddrLn1());
                    pst.setString(6, customer.getAddrLn2());
                    pst.setString(7, customer.getZip());
                    pst.setString(8, customer.getCity());
                    pst.setString(9, customer.getState());
                    pst.setString(10, customer.getCountry());
                    pst.setString(11, customer.getTel());
                    pst.setString(12, customer.getCel());
                    pst.setString(13, customer.getFax());
                    pst.setString(14, customer.getEmail());
                    pst.setString(15, customer.getWww());
                    pst.setString(16, customer.getBank());
                    pst.setString(17, customer.getBankNumber());
                    pst.setString(18, customer.getPaymentBy());
                    pst.setString(19, customer.getCardName());
                    pst.setString(20, customer.getCardNumber());
                    pst.setString(21, customer.getCardExpiryDate());
                    pst.setString(22, customer.getCardDigiCode());
                    pst.setString(23, customer.getNotes());
                    pst.setString(24, customer.getLastUserId());
                    pst.setString(25, (new WIDate()).getDateTimeDB());
                    pst.setInt(26, customer.getSalesPersonId().intValue());
                    pst.setInt(27, customer.getId().intValue());
                    pst.setString(28, customer.getLastTime());
                    pst.execute();
                    return pst.getUpdateCount() != 0;

                case 9: // '\t'
                    salesPerson = (SalesPerson) obj;
                    pst = con.prepareStatement("UPDATE SALESPERSON SET ID=?,LASTNAME=?,FIRSTNAME=?,AREA=?,TEL=?,CEL=?,FAX=?,EMAIL=?,LEVEL=?,SALESRATE=?,TOTALSALESRATE=?,RESULTS=?,RESULTSDATE=?,OBJECTIF=?,OBJECTIFDATE=?,NOTES=?,LASTUSERID=?,LASTTIME=?,SALESPERSONFAMILY=?,ADDRLN1=?,ADDRLN2=?,ZIP=?,CITY=?,COUNTRY=? WHERE ID=? AND LASTTIME=?");
                    pst.setInt(1, salesPerson.getId().intValue());
                    pst.setString(2, salesPerson.getLastname());
                    pst.setString(3, salesPerson.getFirstname());
                    pst.setString(4, salesPerson.getArea());
                    pst.setString(5, salesPerson.getTel());
                    pst.setString(6, salesPerson.getCel());
                    pst.setString(7, salesPerson.getFax());
                    pst.setString(8, salesPerson.getEmail());
                    pst.setString(9, salesPerson.getLevel());
                    pst.setBigDecimal(10, salesPerson.getSalesRate());
                    pst.setBigDecimal(11, salesPerson.getTotalSalesRate());
                    pst.setBigDecimal(12, salesPerson.getResults());
                    pst.setString(13, date.toDB(salesPerson.getResultsDate()));
                    pst.setBigDecimal(14, salesPerson.getObjectif());
                    pst.setString(15, date.toDB(salesPerson.getObjectifDate()));
                    pst.setString(16, salesPerson.getNotes());
                    pst.setString(17, salesPerson.getLastUserId());
                    pst.setString(18, (new WIDate()).getDateTimeDB());
                    pst.setString(19, salesPerson.getSalesPersonFamily());
                    pst.setString(20, salesPerson.getAddrLn1());
                    pst.setString(21, salesPerson.getAddrLn2());
                    pst.setString(22, salesPerson.getZip());
                    pst.setString(23, salesPerson.getCity());
                    pst.setString(24, salesPerson.getCountry());
                    pst.setInt(25, salesPerson.getId().intValue());
                    pst.setString(26, salesPerson.getLastTime());
                    pst.execute();
                    return pst.getUpdateCount() != 0;

                case 2: // '\002'
                case 3: // '\003'
                default:
                    return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    private boolean deleteSQL(Integer pk, String lastUpdate, int type) {
        try {
            switch (type) {
                case 1: // '\001'
                    pst = con.prepareStatement("DELETE FROM USER WHERE ID=? AND LAST_UPDATE=?");
                    pst.setInt(1, pk.intValue());
                    pst.setString(2, lastUpdate);
                    pst.execute();
                    return true;

                case 4: // '\004'
                    pst = con.prepareStatement("DELETE FROM REALESTATE WHERE ID=? AND LAST_UPDATE=?");
                    pst.setInt(1, pk.intValue());
                    pst.setString(2, lastUpdate);
                    pst.execute();
                    return true;

                case 5: // '\005'
                    pst = con.prepareStatement("DELETE FROM AGENCY WHERE ID=? AND LAST_UPDATE=?");
                    pst.setInt(1, pk.intValue());
                    pst.setString(2, lastUpdate);
                    pst.execute();
                    return cascadeDeleteAgency(pk);

                case 6: // '\006'
                    pst = con.prepareStatement("DELETE FROM GROUPS WHERE ID=? AND LAST_UPDATE=?");
                    pst.setInt(1, pk.intValue());
                    pst.setString(2, lastUpdate);
                    pst.execute();
                    return cascadeDeleteGroup(pk);

                case 7: // '\007'
                    pst = con.prepareStatement("DELETE FROM AVAILABILITY WHERE ID=? AND LAST_UPDATE=?");
                    pst.setInt(1, pk.intValue());
                    pst.setString(2, lastUpdate);
                    pst.execute();
                    return true;

                case 8: // '\b'
                    pst = con.prepareStatement("DELETE FROM CUSTOMER WHERE ID=? AND LASTTIME=?");
                    pst.setInt(1, pk.intValue());
                    pst.setString(2, lastUpdate);
                    pst.execute();
                    return true;

                case 9: // '\t'
                    pst = con.prepareStatement("DELETE FROM SALESPERSON WHERE ID=? AND LASTTIME=?");
                    pst.setInt(1, pk.intValue());
                    pst.setString(2, lastUpdate);
                    pst.execute();
                    return true;

                case 2: // '\002'
                case 3: // '\003'
                default:
                    return false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    private boolean cascadeDeleteAgency(Integer pk) {
        try {
            pst = con.prepareStatement("DELETE FROM USER WHERE AGENCY_ID=?");
            pst.setInt(1, pk.intValue());
            pst.execute();
            pst = con.prepareStatement("DELETE FROM REALESTATE WHERE AGENCY_ID=?");
            pst.setInt(1, pk.intValue());
            pst.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    private boolean cascadeDeleteGroup(Integer pk) {
        try {
            pst = con.prepareStatement("DELETE FROM USER WHERE GROUP_ID=?");
            pst.setInt(1, pk.intValue());
            pst.execute();
            pst = con.prepareStatement("SELECT ID FROM AGENCY WHERE GROUP_ID=?");
            pst.setInt(1, pk.intValue());
            rs = pst.executeQuery();
            if (rs.next()) {
                do {
                    cascadeDeleteAgency(new Integer(rs.getInt(1)));
                } while (rs.next());
            }
            pst = con.prepareStatement("DELETE FROM AGENCY WHERE GROUP_ID=?");
            pst.setInt(1, pk.intValue());
            pst.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    private Integer getNewId(String request) {
        try {
            SQLList list = new SQLList();
            if (list.search(request)) {
                Vector rows = list.getRows();
                if ((String) rows.firstElement() == null) {
                    return new Integer(1);
                } else {
                    return new Integer((String) rows.firstElement());
                }
            } else {
                return null;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    private User user;
    private RealEstate realEstate;
    private Agency agency;
    private Group group;
    private Availability availability;
    private Customer customer;
    private SalesPerson salesPerson;
    private PreparedStatement pst;
    private ResultSet rs;
    private Connection con;
    private WIDate date;
}
